﻿namespace SPQueryToSql
{
    internal static class SQL
    {
        public const string LookupSingleFormat = @" OUTER APPLY (SELECT CONVERT(NVARCHAR(10), UserData.tp_Id) + ';#' + CONVERT(NVARCHAR(MAX), UserData.[{1}]) + ';#' FROM [dbo].[AllUserData] UserData (NOLOCK) WHERE UserData.tp_DeleteTransactionId = 0x  AND UserData.tp_IsCurrentVersion = 1 AND UserData.tp_ListId = '{0}' AND UserData.[{3}] IS NOT NULL AND UserData_{4}.tp_ID = UserData.[tp_ID]) [Lookup_{2}](Val)";

        public const string LookupMultiFormat = @" OUTER APPLY (SELECT CONVERT(NVARCHAR(10), UserData.tp_Id) + ';#' + CONVERT(NVARCHAR(MAX), UserData.[{2}]) + ';#' FROM [dbo].[AllUserDataJunctions] Junctions (NOLOCK), [dbo].[AllUserData] UserDataLeft (NOLOCK), [dbo].[AllUserData] UserData (NOLOCK) WHERE Junctions.tp_DeleteTransactionId = 0x AND UserDataLeft.tp_DeleteTransactionId = 0x AND UserData.tp_DeleteTransactionId = 0x AND Junctions.tp_IsCurrentVersion = 1 AND UserDataLeft.tp_IsCurrentVersion = 1 AND UserData.tp_IsCurrentVersion = 1 AND Junctions.tp_SourceListId = '{0}' AND Junctions.tp_SourceListId = UserData.tp_ListId AND Junctions.tp_Id = UserData.tp_ID AND Junctions.tp_FieldId = '{1}' AND Junctions.tp_DocId = UserDataLeft.tp_DocId AND UserData_{4}.tp_ID = UserDataLeft.[tp_ID] FOR XML PATH('')) [Lookup_{3}](Val)";

        public const string UserMultiFormat = @" OUTER APPLY (SELECT CONVERT(NVARCHAR(10), j.tp_Id) + ';#' + f.Title + ';#' FROM [dbo].[AllUserData] d (NOLOCK) LEFT JOIN [dbo].[AllUserDataJunctions] j (NOLOCK) ON d.tp_DocId = d.tp_DocId LEFT JOIN (SELECT u.tp_Id, u.tp_SiteID SiteID, u.tp_Title Title FROM [dbo].[UserInfo] u (NOLOCK) WHERE u.tp_SiteID = '{0}' UNION SELECT g.ID, g.SiteID, g.Title Title FROM [dbo].[Groups] g (NOLOCK) WHERE g.[SiteID] = '{0}') F on f.tp_ID = j.tp_Id WHERE j.tp_IsCurrentVersion = 1 AND d.tp_IsCurrentVersion = 1 AND j.tp_FieldId = '{1}' AND j.tp_DocId = d.tp_DocId AND d.tp_RowOrdinal = {3} AND d.tp_Id = UserData_{3}.tp_ID FOR XML PATH('')) [User_{2}](Val)";

        public const string UserSingleFormat = @" OUTER APPLY (SELECT CASE WHEN EXISTS(SELECT TOP 1 1 FROM [dbo].[UserInfo] WHERE [tp_SiteID] = '{0}' AND tp_Id = UserData_{2}.tp_ID) THEN (SELECT TOP 1 CONVERT(NVARCHAR(10), tp_Id) + ';#' + tp_Title + ';#' FROM [dbo].[UserInfo] WHERE [tp_SiteID] = '{0}' AND tp_Id = UserData_{2}.tp_ID) ELSE (SELECT TOP 1 CONVERT(NVARCHAR(10), ID) + ';#' + Title + ';#' FROM [dbo].[Groups] WHERE [SiteId] = '{0}' AND ID = UserData_{2}.tp_ID) END FOR XML PATH('')) [User_{1}](Val)";
    }
}
